Varias formas de importar datos de SQLServer por lotes
Varias formas de importar datos de SQLServer por lotes
Hablando de la importación por lotes de datos de SQLServer, comparemos varios métodos de uso común y luego presentemos en detalle cómo usar cada método ( El código en este el artículo usa el lenguaje c#)
Método de importación
Si es necesario escribir código
¿Se pueden procesar los datos durante el proceso de importación?
Velocidad de inserción de datos
Importación de datos de múltiples tablas
Si es necesario escribir sentencias SQL
A través de la herramienta de administración de clientes SQLServer
.F
p>F
Rápido
F
F
2. insertar sentencias o procedimientos almacenados
T
T
Lento
T
T
3. Utilice SqlBulkCopy
T
F
Rápido
F
F
4. Utilice el parámetro de valor de las tablas de SQLServer
T
T
rápido
T
T
1. A través de la herramienta de administración de clientes SQLServer
Abra el cliente SQLServer para conectarse al motor de base de datos que desea operar
Haga clic derecho en la base de datos que desee para operar, seleccione Tarea - Importar datos, aparecerá una ventana emergente la primera vez que lo use. La página del asistente se muestra a continuación:
Haga clic en Siguiente. Generalmente, los datos que se importarán son. excel. Elegimos Microsoft Excel como fuente de datos (habrá algunas diferencias en las diferentes versiones).
Examine y seleccione el archivo de Excel que desea importar;
Siguiente paso. seleccione la fuente de datos de destino y seleccione nuestro SQLServer
Haga clic en Siguiente según sea necesario. Debe prestar atención a la página de selección de tabla y fuente de datos, seleccionar la tabla correspondiente de acuerdo con las necesidades reales y editarla. asignación de columnas,
Finalmente haga clic en Finalizar para importar los datos.
2. Llame cíclicamente a la instrucción de inserción o al procedimiento almacenado.
Este método consiste en llamar a la instrucción SQL escrita o al procedimiento almacenado para insertar datos cíclicamente en la base de datos y puede leerlos según sea necesario; Después de archivar los datos, los datos se verifican y procesan.
El siguiente código es una implementación de la inserción de bucle. Si necesita generar el progreso de la importación, puede usar BackgroundWorker ProgressBar para mostrar el progreso de la importación en la página.
private int DataImport; ()
{
if (File.Exists(ruta))
File.Delete(ruta);
int i = 0;
// Abre la conexión a la base de datos
string strConn = System.Configuration.ConfigurationManager.AppSettings["SsConnString"];
SqlConnection Coon = new SqlConnection (strConn);
if (Coon.State.Equals(ConnectionState.Closed))
{
Coon.Open();
}
foreach (DataRow dr en m_dt.Rows)
{
i;
if (bkWorker.CancellationPending)
{
p>
e.Cancel = true;
return -1; > SqlParameter[] parms = {
nuevo SqlParameter("@Name", SqlDbType.VarChar, 32),
nuevo SqlParameter("@Sheng", SqlDbType.VarChar),
nuevo SqlParameter("@City", SqlDbType.VarChar),
nuevo SqlParameter("@Xian", SqlDbType.VarChar)
}; >
intentar
{
parms[0].Value = (dr["name"] "").Trim();
parms [7].Valor = dr["provincia"] "" ;
parms[8].Valor = dr["ciudad"] "";
parms[9]. Valor = dr["condado"] "";
}
catch (Excepción)
{
MessageBox.Show(" ¡Asegúrese de que los nombres de las columnas en la tabla de datos sean consistentes con los de la plantilla! ", "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Warning);
devolver -1;
}
intentar
{
SqlCommand Cmd = Tools.CreateCmd("P_Data_Import", parms, Conn);
int iRet = Cmd.ExecuteNonQuery()
if (iRet =; = 0)
{
continuar;
}
}
catch (Excepción ex)
{
Tools.Log_Write("th" (m_dt.Rows.IndexOf(dr) 1).ToString() "Error de importación de fila:" ex.Message, "d:\\data import log.txt"); >
}
}
if (MessageBox.Show("¡Importación de datos completada!, ¡Abrir registro de importación!", "Preguntar") == DialogResult.OK)
{
this.buttonImport.Enabled = true;
if (File.Exists(path))
System.Diagnostics.Process. Inicio(ruta);
}
Conn.Close();
retorno
}
//Abrir conexión de base de datos
SqlConnection estática pública ReturnConn()
{
cadena strConn = "servidor=dirección de base de datos; uid=cuenta de base de datos; contraseña =contraseña; base de datos=nombre de la base de datos"
SqlConnection Coon = new SqlConnection(strConn);
if (Coon.State.Equals(ConnectionState.Closed))
{
Coon.Open();
}
devuelve Coon;
}
// Ejecutar un procedimiento almacenado con parámetros
public static SqlCommand CreateCmd(string procName, SqlParameter[] prams, SqlConnection Conn)
{
SqlConnection SqlConn = Conn;
if (SqlConn.State.Equals(ConnectionState.Closed))
{
SqlConn.Open()
}
SqlCommand Cmd = nuevo SqlCommand();
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Connection = SqlConn
Cmd. CommandText = procName;
if (cochecitos != null)
{
foreach (parámetro SqlParameter en cochecitos)
{ p>
if (parámetro != null)
{
Cmd.Parameters.Add(parámetro);
}
}
}
>
return Cmd;
}
3. Utilice SqlBulkCopy
A continuación se toma como ejemplo la importación de datos de cancelación de estudiantes, cómo importar datos y acerca de. SqlBulkCopy (Explicación oficial: le permite cargar por lotes de manera eficiente tablas de SQL Server utilizando datos de otras fuentes.
) se puede utilizar buscando en Internet. Hay mucha información y el documento oficial es un enlace directo.
Primero, debe construir un objeto de tipo DataTable (TransferData) para importar el formato de datos. y en segundo lugar, establezca la asignación de columnas con la fuente de datos. Relación
El cronómetro se utiliza para calcular el tiempo necesario para importar datos
private void InsertTwo()
. {
OpenFileDialog fd = new OpenFileDialog() ;
if (fd.ShowDialog() != DialogResult.OK)
{
return;
}
Cronómetro sw = new Cronómetro();
//Construir columna para tabla de datos
DataTable dt = Herramientas .TransferData(fd.FileName, "sheet1", "");
dt.Columns.Add(xhFee);
this.btn_Import.Enabled = false
string strConn = System.Configuration.ConfigurationManager.AppSettings[" SsConnString"];
usando (SqlConnection conn = new SqlConnection(strConn))
{
SqlBulkCopy BulkCopy = new SqlBulkCopy(conn);
BulkCopy.DestinationTableName = "T_FI_IncomeDetail"
BulkCopy.BatchSize = dt.Rows.Count
; //Establecer correspondencia de columnas
BulkCopy.ColumnMappings.Add("Tipo de tutorial", "FdId");
BulkCopy.ColumnMappings.Add("Consumo de lecciones o lecciones", " XhKeshi");
BulkCopy.ColumnMappings .Add("Fecha de consumo", "CreateTime");
BulkCopy.ColumnMappings.Add("Nombre del estudiante", "Nombre");
conn.Open();
sw.Start();
int totalRow = dt.Rows.Count
if; (dt != null amp; dt.Rows.Count != 0)
{
dateTimeDelete.Value = Convert.ToDateTime(dt.Rows[0]["Fecha de consumo" ]);
BulkCopy.WriteToServer(dt);
sw.Stop();
}
MessageBox.Show(string). .Format("Insertar {0} registros** *Tardó {1}ms", totalRow, sw.Elap
sedMillisegundos));
}
}
// Obtener datos de Excel y completarlos en DataTable
public static TransferData(string excelFile , cadena nombre de hoja, cadena strScreen)
{
System.Data.DataTable dt = new System.Data.DataTable();
prueba
{
//Obtener todos los datos
string strConn = "Provider=Microsoft.Ace.Oledb.12.0; Data Source=" excelFile "; Propiedades extendidas='Excel 12.0 ; HDR=YES;IMEX=1;'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbDataAdapter myCommand = null;
myCommand = new OleDbDataAdapter("Seleccione * de [Sheet1$] " strScreen, strConn
myCommand.Fill(dt); conn .Close();
}
catch (Excepción ex)
{
MessageBox.Show(ex.Message);
}
return dt;
}
4. Utilice parámetros con valores de tabla de SQLServer
Este método es necesario Al importar datos en lotes, es necesario realizar operaciones lógicas en los datos, lo cual es especialmente práctico cuando afecta a varias tablas. En este ejemplo, se utiliza un cursor para importar datos de usuario. se utiliza para procesar 2000 datos en el proceso de almacenamiento. Se puede completar en 2 segundos.
Principalmente al aplicar los parámetros de tipo de tabla de SQL Server, al pasar datos de la tabla al procedimiento almacenado, todas las operaciones SQL se pueden realizar en la base de datos, mejorando el rendimiento operativo.
Primero, cree un tipo de tabla personalizada en la base de datos. El formato de la declaración de creación es el siguiente:
CREATE TYPE [dbo].[UserInfo] AS TABLE(
[Nombre] [varchar](32) NULL,
[Código] [varchar](32) NULL,
[Género] [varchar](32) NULL,
[Cumpleaños] [fecha y hora] NULL
)
Cómo utilizar procedimientos almacenados: UserInfo es un tipo de tabla personalizada creada con antelación
crear proc procName
(
@DataTable UserInfo solo lectura
)
como
comenzar
-- Implemente su propia lógica y use @DataTable de la misma manera que una tabla normal.
-- Se recomienda que si necesita unir tablas para filtrar datos para @DataTable, utilice una tabla temporal; de lo contrario, es posible que se le solicite que exceda el tiempo máximo de consulta establecido por la base de datos
--(Puede verlo en el motor de la base de datos, haga clic con el botón derecho en Propiedades--Conexión. Utilice el regulador de consultas para evitar que el tiempo de consulta sea demasiado largo, y el valor predeterminado es 30 segundos si no se marca),
-- Se recomienda que las operaciones lógicas se procesen en código, no en procedimientos almacenados, para reducir el tiempo de consulta. presión sobre el servidor de la base de datos
end
Ejemplo de llamada de código:
private int DataImport1(remitente del objeto)
{
Cronómetro sw = new Cronómetro();
sw.Start();
ruta de cadena = "d:\\data import log.txt";
if (File.Exists(ruta))
File.Delete(ruta);
int count = m_dt.Rows.Count;
SqlConnection Conn = SsZongs.ReturnConn();
DataTable dt = new DataTable("userIfo");
dt.Columns.Add("Nombre", tipode(cadena));
dt.Columns.Add("Código", tipo de (cadena));
dt.Columns.Add("Género", tipo de (cadena));
dt.Columns.Add("Cumpleaños", typeof(DateTime));
prueba
{
for (int i = 0; i lt ; contar; i )
{
intentar
{
dt.Rows.Add((m_dt.Rows[i][ "Nombre"] "").Trim(),
m_dt.Rows[i][" Número"].ToString(),
m_dt.Rows[i][" Género"].ToString(),
m_dt.Rows[i]["Fecha de nacimiento"]
);
}
catch (Exception)
{
MessageBox.Show ("¡Asegúrese de que los nombres de las columnas en la tabla de datos sean consistentes con los de la plantilla!" , "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return -1;
}
}
Herramientas. insertTableToDB(dt, " procName");
}
catch (Excepción ex)
{
Tools.Log_Write(ex. Mensaje, "d: \\data import log.txt");
}
finalmente
{
sw.Stop( );
if (MessageBox.Show("¡Importación de datos completada! Consume mucho tiempo" sw.ElapsedMillisegundos "¡Milisegundos, abra el registro de importación!", "Preguntar") == DialogResult.OK)
{
this.buttonImport.Enabled = true;
if (File.Exists(path))
System.Diagnostics.Process.Start( ruta);
}
}
return -1;
}
insertTableToDB vacío estático público ( System.Data.DataTable dt , cadena procName)
{
SqlConnection sqlCon = SsZongs.ReturnConn();
usando (var cmd = new SqlCommand(procName , sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter("@DataTable", SqlDbType.Structured) { Valor = dt };
cmd.Parameters.Add(param
cmd.ExecuteNonQuery()
}
sqlCon; .Close();
}
Resumen
He utilizado los métodos anteriores en el trabajo real para necesidades comerciales específicas para elegir la solución adecuada según la situación. .
No tengo buenas habilidades para redactar documentos, pero puedo comunicarme en cualquier momento si es necesario.
Mis Nuggets
Varias formas de importar datos de SQLServer por lotes
Etiqueta: información ide de imagen de blebatchtail del servidor de base de datos elfuse