SQL相关知识

最新推荐文章于 2023-03-28 13:10:30 发布

yank2009

最新推荐文章于 2023-03-28 13:10:30 发布

阅读量340

点赞数

分类专栏： C# 文章标签： C# sql

本文链接：https://blog.csdn.net/yank2009/article/details/51672624

版权

C# 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、连接字符串

使用用户名和密码：

Data Source=LocalHost; Initial Catalog=myDataBase; User ID=myUsername; Password=myPassword

Server = LocalHost; Database = myDataBase; User ID=myUsername; Password=myPassword

使用Windows身份验证登录：

Data Source=LocalHost; Initial Catalog=myDataBase; Integrated Security=SSPI

Server = LocalHost; Database = myDataBase; Integrated Security=SSPI

从App.config获取连接字符串：

C# Code:

public static string connStr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

App.config:

<?xml version="1.0" encoding="utf-8" ?>

</connectionStrings>

</configuration>

2、数据类型

char：固定长度，存储ANSI字符，不足的补英文半角空格。

nchar：固定长度，存储Unicode字符，不足的补英文半角空格

varchar：可变长度，存储ANSI字符，根据数据长度自动变化。

nvarchar：可变长度，存储Unicode字符，根据数据长度自动变化。

3、SQL语句

选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ‘%value1%'
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

较详细的SQL语句：

http://www.cnblogs.com/yubinfeng/archive/2010/11/02/1867386.html

4、SQLHelper

简单的SQLHelper类，不是很全面，仅用于个人学习和使用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace SqlStudy
{
    class SQLHelper
    {
        public static string connStr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }

        public static object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteScalar();
            }
        }

        public static DataSet ExecuteDataset(string cmdText, CommandType cmdType, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                cmd.Parameters.AddRange(parameters);
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    return ds;
                }
            }
        }
    }
}